package com.dao.impl;

import java.io.Serializable;
import java.util.List;

import org.hibernate.Query;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.dao.BindingDao;
import com.enetiy.Areausable;
import com.enetiy.Binding;
import com.enetiy.Comboinfo;

public class BindingDaoImpl extends HibernateDaoSupport implements BindingDao {

	
	public Serializable insertBinding(Binding bin) throws Exception {
		return this.getHibernateTemplate().save(bin);
	}

	
	public void deleteBingding(Binding bin) throws Exception {
		this.getHibernateTemplate().delete(bin);
	}
	
	
	public List<Binding> selsctBindingUsers(int uid) throws Exception {
		Query query = this.getSession().createQuery("from Binding where users_id=?");
		query.setParameter(0, uid);
		List<Binding> list = query.list();
		return list;
	}

	
	public Binding selsctBindingUsers(int usersid, int areaid) throws Exception {
		String sql= "from Binding where users_id=? and areainfo_id=?";
		Query query = this.getSession().createQuery(sql);
		query.setParameter(0, usersid);
		query.setParameter(1, areaid);
		List <Binding> list = query.list();
		return list.isEmpty()?null:list.get(0);
	}


	@Override
	public Binding selectBindingAcct(String acct,int areainfo_id) throws Exception {
		String sql= "from Binding where account=? and areainfo_id=?";
		Query query = this.getSession().createQuery(sql);
		query.setParameter(0, acct);
		query.setParameter(1, areainfo_id);
		List <Binding> list = query.list();
		return list.isEmpty()?null:list.get(0);
	}


	@Override
	public int selectRadiusNum(int radiusId) throws Exception {
		String sql = "from Binding where radius_id=?";
		Query query = this.getSession().createQuery(sql);
		query.setParameter(0, radiusId);
		List<Binding> list = query.list();
		return list.isEmpty()?0:list.size();
	}


	@Override
	public void updateBinding(Binding bin) throws Exception {
		this.getHibernateTemplate().update(bin);
		
	}


	@Override
	public List<Binding> selectBindingAvailable(int userid,
			List<Areausable> areabusable) throws Exception {
			String sql = "from Binding where users_id = ? ";
		for (int i = 0; i < areabusable.size(); i++) {
			sql=sql+"and areabinding_id=?";
		}
		sql=sql+" ORDER BY bandwidth DESC";
		Query query = this.getSession().createQuery(sql);
		query.setParameter(0, userid);
		
		
		List<Binding> list = query.list();
		return list;
	}


	@Override
	public Binding selsctbinding(String acct, int areaid, int userid)
			throws Exception {
		String sql= "";
		if(acct!=null&&!acct.equals("")){
			sql= "from Binding where area_id=? and users_id=?";
		}else{
			sql= "from Binding where  area_id=? and users_id=? and account=? ";
		}
		Query query = this.getSession().createQuery(sql);
		query.setParameter(0, areaid);
		query.setParameter(1, userid);
		if(acct!=null&&!acct.equals(""))
			query.setParameter(2, acct);
		List <Binding> list = query.list();
		return list.isEmpty()?null:list.get(0);
	}

	@Override
	public Binding selectBinding(int bindingid) throws Exception {
		return this.getHibernateTemplate().get(Binding.class, bindingid);
	}


	@Override
	public Binding selectbinding(String acct, int areaid) throws Exception {
		String sql = "from Binding where account = ? and area_id = ?";
		Query query = this.getSession().createQuery(sql);
		query.setParameter(0, acct);
		query.setParameter(1, areaid);
		List <Binding> list = query.list();
		return list.isEmpty()?null:list.get(0);
	}


	@Override
	public Binding selectbinding(int userid, int areaid) throws Exception {
		String sql = "from Binding where users_id = ? and area_id = ?";
		Query query = this.getSession().createQuery(sql);
		query.setParameter(0, userid);
		query.setParameter(1, areaid);
		List <Binding> list = query.list();
		return list.isEmpty()?null:list.get(0);
	}


	@Override
	public Binding selsctbinding(String acct, int areaid, String pwd)
			throws Exception {
		String sql = "from Binding where account = ? and area_id = ? and account_pwd=?";
		Query query = this.getSession().createQuery(sql);
		query.setParameter(0, acct);
		query.setParameter(1, areaid);
		query.setParameter(2, pwd);
		List <Binding> list = query.list();
		return list.isEmpty()?null:list.get(0);
	}
	
}
